package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;

import model.ItemType;
import model.ItemTypeMeasurement;

public class DBItemType implements IFItemType
{
	private Connection con;

	public DBItemType()
	{
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public int createItemType(ItemType item) throws SQLException 
	{
		int rc = -1;
		ResultSet results;

		String query = "SELECT it_id FROM item_type WHERE it_name = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("createItemType", query);

		try 
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, item.getTypeName());

			results = ps.executeQuery();

			if (!results.next())
			{
				query = "INSERT INTO item_type(it_name, it_measurement_id) VALUES(?, ?)";

				if (DBConnection.doDebug())
					DBConnection.printDebug("createItemType", query);

				try 
				{
					ps = con.prepareStatement(query);

					ps.setString(1, item.getTypeName());
					ps.setInt(2, item.getTypeMeasurement().getID());

					rc = ps.executeUpdate();
				}
				catch (Exception e)
				{
					if (DBConnection.doDebug())
					{
						System.out.println("[ERROR] Query exception - createItemType - INSERT: " + e);
						e.printStackTrace();
					}
				}
			}

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - createItemType - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return rc;
	}

	@Override
	public int updateItemType(ItemType type) throws SQLException
	{
		int rc = -1;

		String query = "SELECT it_id FROM item_type WHERE it_name = ? AND it_id != ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("updateItemType", query);

		try 
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, type.getTypeName());
			ps.setInt(2, type.getTypeID());

			ResultSet results = ps.executeQuery();

			if (!results.next())
			{
				query = "UPDATE item_type SET it_name = ?, it_measurement_id = ? WHERE it_id = ?";

				if (DBConnection.doDebug())
					DBConnection.printDebug("updateItemType", query);

				try 
				{
					ps = con.prepareStatement(query);

					ps.setString(1, type.getTypeName());
					ps.setInt(2, type.getTypeMeasurement().getID());
					ps.setInt(3, type.getTypeID());

					rc = ps.executeUpdate();
				}
				catch (Exception e)
				{
					if (DBConnection.doDebug())
					{
						System.out.println("[ERROR] Query exception - updateItemType - UPDATE: " + e);
						e.printStackTrace();
					}
				}
			}

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - updateItemType - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return rc;
	}

	@Override
	public ArrayList<ItemType> printAllItemTypes() throws SQLException
	{
		ArrayList<ItemType> itemTypesList = new ArrayList<ItemType>();

		String query = "SELECT it.*, im.im_measurement FROM item_type AS it LEFT JOIN item_measurement AS im ON it.it_measurement_id = im.im_id ORDER BY it_name ASC";

		if (DBConnection.doDebug())
			DBConnection.printDebug("printAllItemTypes", query);

		try 
		{	
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);

			ResultSet results = stmt.executeQuery(query);

			while(results.next())
			{				
				ItemType newItemType = buildItemTypeObject(results);

				itemTypesList.add(newItemType);
			}

			stmt.close();     
		}
		catch(Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - printAllItemTypes - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return itemTypesList;
	}

	@Override
	public ItemType findItemTypeByName(String name) throws SQLException
	{
		ItemType newItemType = new ItemType();

		String query = "SELECT it.*, im.* FROM item_type AS it LEFT JOIN item_measurement AS im ON it.it_measurement_id = im.im_id WHERE it.it_name = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("findItemTypeByName", query);

		try 
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, name);

			ResultSet results = ps.executeQuery();

			if (results.next())
				newItemType = buildItemTypeObject(results);

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - findItemTypeByName - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return newItemType;
	}

	private ItemType buildItemTypeObject(ResultSet results) throws SQLException
	{
		ItemType newItemType = new ItemType();

		newItemType.setTypeID(results.getInt("it_id"));
		newItemType.setTypeName(results.getString("it_name"));
		newItemType.setTypeMeasurement(new ItemTypeMeasurement(results.getInt("it_measurement_id"), results.getString("im_measurement")));

		return newItemType;
	}
}